<?php
/*
 * description：
 * author：wh
 * email：
 * createTime：{2020/12/18} {10:53} 
 */

namespace app\webautocode\controller;


use app\webautocode\extend\controller\BaseAdmin;
use app\webautocode\extend\service\DatasService;
use think\Db;

class Mysql extends BaseAdmin
{
    function index(){

        return $this->fetch();
    }
    //region 表管理
    /**        ============================[分割线][表管理]==========================        */
    /**
     * 指定当前数据表
     * @var string
     */
    public $table = 'web_tablemanage';

    public $tablefield = 'web_fieldmanage';

    //数据表列表
    function tablemanage(){
        $this->title = '添加表';
        list($get, $db) = [$this->request->get(), DB::table($this->table)];
        foreach (['title', 'tablename'] as $key) {
            (isset($get[$key]) && $get[$key] !== '') && $db->whereLike($key, "%{$get[$key]}%");
        }
        return parent::_list($db->order('id desc')->where(['is_deleted' => '0']));
    }
    //新增表数据
    function tableadd(){
        $result = $this->_form_data($this->table, 'tableform', '', [], [], true);
        if(request()->isPost()){
            if ($result !== false) {
                return $this->success('操作成功');
            }
            return $this->error('参数错误');
        }
        return $result;
    }

    //编辑表数据
    function tableedit(){
        if(request()->isPost()){
            $data = Db::table($this->table)->where(['id'=>input('id')])->find();
            //存在才修改表名
            if(in_array($data['tablename'], $this->getTables())){
                $this->updateTableName($data['tablename'], input('tablename'));
            }
        }
        $result = $this->_form_data($this->table, 'tableform', '', [], [], true);
        if(request()->isPost()){
            if ($result !== false) {
                return $this->success('操作成功');
            }
            return $this->error('参数错误');
        }
        return $result;
    }
    //删除表数据
    public function tabledel()
    {
        if (in_array('10000', explode(',', $this->request->post('id')))) {
            return $this->error('系统超级账号禁止删除！');
        }
        if (DatasService::update($this->table, [], true)) {
            return $this->success("删除成功");
        }
        return $this->error("删除失败，请稍候再试！");
    }

    /**
     * desc：创建表
     * author：wh
     */
    public function tocreatetable(){
        if(empty(input('id'))) return $this->error("参数空");
        $data = Db::table($this->table)->where(['is_deleted'=>0,'id'=>input('id')])->find();
        if(empty($data)) return $this->error("未获取到数据.".input('id'));
        $this->createTable($data);
        return $this->success("操作成功");
    }
    //创建表
    protected function createTable($data){
        $tablename = $data['tablename'];
        $title = $data['title'];
        //创建表
        //is_deleted tinyint(1) DEFAULT 0 COMMENT '是否删除:1=是,0=否',
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename}(
		id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
		create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
		update_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
		PRIMARY key(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='{$title}' ;";

        $exists_tables = $this->getTables();
        if(false === DB::table($this->table)->execute($sql)){
            echo $sql;
            $this->error('表名创建失败', '', '', 60);
        }else{
            //table不存在，记录
            if(!in_array($tablename, $exists_tables)){
                $sql_file = $tablename;
                DatasService::auto_sync_sql($sql, '创建数据表：'.$tablename, ['sql_file'=>$sql_file,'file'=>__FILE__, 'line'=>__LINE__]);
            }
        }
    }
    //改表注释
    function changetablecomment(){
        $data = Db::table($this->table)->where(['id'=>input('id')])->find();
        $tablename = $data['tablename'];
        $comment = $data['title'];
        //验证
        if(!in_array($tablename, $this->getTables())) return $this->error("数据表不存在.".$tablename);
        $this->updateComment($tablename, $comment);
        return $this->success("操作成功");
    }
    //改注释
    protected function updateComment($tablename, $comment){
        $sql = "ALTER TABLE {$tablename} COMMENT '{$comment}';";
        Db::execute($sql);

        $title = '改注释'.$tablename;
        DatasService::auto_sync_sql($sql, $title, ['sql_file'=>$tablename,'file'=>__FILE__, 'line'=>__LINE__]);
    }
    //改表名
    protected function updateTableName($tablename, $new_table_name){
        $sql = "ALTER TABLE {$tablename} RENAME TO {$new_table_name}";
        Db::execute($sql);

        $title = '改表名'.$tablename;
        DatasService::auto_sync_sql($sql, $title, ['sql_file'=>$tablename,'file'=>__FILE__, 'line'=>__LINE__]);
    }

    /**        ============================[分割线][表管理]==========================        */
    //endregion 表管理













    //region 字段管理
    /**        ============================[分割线][字段管理]==========================        */
    //字段列表
    function fieldmanage(){
        $this->title = '添加字段';
        list($get, $db) = [$this->request->get(), DB::table($this->tablefield)];
        foreach (['title', 'fields_name', 'type', 'tablename', 'tabletitle'] as $key) {
            (isset($get[$key]) && $get[$key] !== '') && $db->whereLike($key, "%{$get[$key]}%");
        }
        return parent::_list($db->order('id desc')->where(['is_deleted' => '0']));
    }
    //新增字段数据
    function fieldadd(){
        $tables_info = DB::table($this->table)->order('id desc')->where(['is_deleted'=>0])->select();
        $this->assign('tables_info', $tables_info);
        $result = $this->_form_data($this->tablefield, 'fieldform', '', [], [], true);
        if(request()->isPost()){
            if ($result !== false) {
                return $this->success('操作成功');
            }
            return $this->error('参数错误');
        }
        return $result;
    }
    //编辑字段数据
    function fieldedit(){
        if(request()->isPost()){
            $data = Db::table($this->tablefield)->where(['id'=>input('id')])->find();
            if(!in_array($data['tablename'], $this->getTables())) return $this->error('数据表不存在.'.$data['tablename']);

            if(!in_array(input('fields_name'), $this->getTableFields($data['tablename'])) && in_array($data['fields_name'], $this->getTableFields($data['tablename']))){
                //删除原字段
                $this->dropFieldName($data['tablename'], $data['fields_name']);
            }
        }
        $tables_info = DB::table($this->table)->order('id desc')->where(['is_deleted'=>0])->select();
        $this->assign('tables_info', $tables_info);
        $result = $this->_form_data($this->tablefield, 'fieldform', '', [], [], true);
        if(request()->isPost()){
            if ($result !== false) {
                return $this->success('操作成功');
            }
            return $this->error('参数错误');
        }
        return $result;
    }

    //删除字段数据
    public function fielddel()
    {
        if (in_array('10000', explode(',', $this->request->post('id')))) {
            return $this->error('系统超级账号禁止删除！');
        }
        if (DatasService::update($this->tablefield, [], true)) {

            $data = Db::table($this->tablefield)->where(['id'=>input('id')])->find();
            if(!in_array($data['tablename'], $this->getTables())) return $this->error('数据表不存在.'.$data['tablename']);

            if(in_array($data['fields_name'], $this->getTableFields($data['tablename']))){
                //删除原字段
                $this->dropFieldName($data['tablename'], $data['fields_name']);
            }
            return $this->success("删除成功");
        }
        return $this->error("删除失败，请稍候再试！");
    }

    /**
     * desc：创建字段
     * author：wh
     */
    function tocreatefield(){
        if(empty(input('id'))) return $this->error("参数空");
        $data = Db::table($this->tablefield)->where(['is_deleted'=>0,'id'=>input('id')])->find();
        if(empty($data)) return $this->error("未获取到数据.".input('id'));
        if ($data['type'] == 'enum' && empty($data['default']))return $this->error("枚举类型默认值必须.".input('id'));
        if ($data['type'] == 'enum' && $data['default'] && false===strpos($data['default'], ':'))return $this->error("枚举类型默认值必须用':'隔开.".input('id'));
        $this->createfield($data);
        return $this->success("操作成功");
    }
    //创建字段
    protected function createfield($table){
        $table_name = $table['tablename'];
        //新增字段
        $dec_num = '';

        if(in_array(strtoupper($table['type']), ['TINYINT','SMALLINT','MEDIUMINT','INT','BIGINT','FLOAT','DOUBLE','DECIMAL'])){
            $table['default'] = '0';//整型默认值 浮点类型自动转为0.00
            if(in_array(strtoupper($table['type']), ['FLOAT','DOUBLE','DECIMAL'])){
                $dec_num = ','.$table['decimals_size'];//浮点类型小数位位数
            }
        }elseif ($table['type'] == 'enum'){
            //枚举值不转换
        }else{
            $table['default'] = '""';
        }

        if(in_array(strtolower($table['type']), ['timestamp','datetime','date','time','text','mediumtext','longtext'])){
            $table['size'] = 0;//重写 默认0
            $table['default'] = 'null';//重写
        }
        //是否可以负数
        if(in_array(strtolower($table['type']), ['int','tinyint','bigint','smallint', 'double', 'float', 'decimal']) && !empty($post['is_unsigned'])){//不是
            $is_unsigned = 'unsigned';
        }else{//是 表单可选值
            $is_unsigned = '';
        }

        if (in_array($table['fields_name'], ['create_time', 'update_time'])){
            if($table['fields_name'] == 'create_time'){
                $table['default'] = 'CURRENT_TIMESTAMP';
            }
            if($table['fields_name'] == 'update_time'){
                $table['default'] = 'NULL ON UPDATE CURRENT_TIMESTAMP';
            }
        }
        //验证字段是否存在
        $f = DB::table($table_name)->getTableFields();
        if(in_array($table['fields_name'], $f)){
            //枚举类型
            if ($table['type'] == 'enum'){
                $enum_str = '';
                $enum_default = '';
                //组合枚举值
                if($table['default']){
                    //$enum_str.='ENUM('.$table['default'].')';
                    $enum_default = '';

                    //拆分冒号格式
                    $spl_sign_arr = explode(':',$table['default']) ;

                    //[特别注意,枚举的可选值,一定要是数字,否则会出错]
                    //枚举sql:  `status` enum('1','2') DEFAULT '1' COMMENT '状态:1=a,2=b',
                    $tmp_spl_str = '';
                    foreach (explode(',',$spl_sign_arr[1])  as $str){
                        if($tmp_spl_str){

                            $tmp_spl_str.= ','. "'".explode('=',$str)[0]."'";
                        }else{
                            $tmp_spl_str.= "'".explode('=',$str)[0]."'";
                            $enum_default = "'".explode('=',$str)[0]."'";
                        }
                    }
                    $enum_str.='ENUM('.$tmp_spl_str.')';
                }
                $sql2 = "ALTER TABLE {$table_name} MODIFY {$table['fields_name']} {$enum_str} NOT NULL DEFAULT {$enum_default} COMMENT '{$table['default']}';";
            }else{
                $sql2 = "ALTER TABLE {$table_name} MODIFY COLUMN {$table['fields_name']} {$table['type']}(".$table['size'].$dec_num.") {$is_unsigned} DEFAULT {$table['default']} COMMENT '{$table['title']}';";
            }
        }else{

            if(in_array($table['type'], ['text','longtext'])){
                $sql2 = "ALTER TABLE {$table_name} ADD COLUMN {$table['fields_name']} {$table['type']} COMMENT '{$table['title']}' AFTER id;";
            }elseif ($table['type'] == 'enum'){//枚举类型
                $enum_str = '';
                //组合枚举值
                if($table['default']){
                    $enum_default = '';

                    //拆分冒号格式
                    $spl_sign_arr = explode(':',$table['default']) ;

                    //[特别注意,枚举的可选值,一定要是数字,否则会出错]
                    //枚举sql:  `status` enum('1','2') DEFAULT '1' COMMENT '状态:1=a,2=b',
                    $tmp_spl_str = '';
                    foreach (explode(',',$spl_sign_arr[1])  as $str){
                        if($tmp_spl_str){

                            $tmp_spl_str.= ','. "'".explode('=',$str)[0]."'";
                        }else{
                            $tmp_spl_str.= "'".explode('=',$str)[0]."'";
                            $enum_default = "'".explode('=',$str)[0]."'";
                        }
                    }
                    $enum_str.='ENUM('.$tmp_spl_str.')';
                }

                $sql2 = "ALTER TABLE {$table_name} ADD {$table['fields_name']} {$enum_str} NOT NULL DEFAULT {$enum_default} COMMENT '{$table['default']}' AFTER id;";
            }else{
                $sql2 = "ALTER TABLE {$table_name} ADD COLUMN {$table['fields_name']} {$table['type']}(".$table['size'].$dec_num.") {$is_unsigned} DEFAULT {$table['default']} COMMENT '{$table['title']}' AFTER id;";
            }
        }

        DB::execute($sql2);

        $title = '新增或更新'.$table['title'];//注释

        DatasService::auto_sync_sql($sql2, $title, ['sql_file'=>$table_name,'file'=>__FILE__, 'line'=>__LINE__]);

    }
    //删除字段
    protected function dropFieldName($tablename, $fieldname){
        $sql = "ALTER TABLE {$tablename} DROP COLUMN {$fieldname};";
        DB::execute($sql);

        $title = '删除字段'.$tablename;
        DatasService::auto_sync_sql($sql, $title, ['sql_file'=>$tablename,'file'=>__FILE__, 'line'=>__LINE__]);
    }



    /**        ============================[分割线][字段管理]==========================        */
    //endregion 字段管理



    /**
     * desc：查询当前数据库所有的表名
     * author：wh
     * @return mixed
     */
    function getTables(){
        return array_column(Db::query('SHOW TABLES;'), 'Tables_in_'.config('database.database'));
    }

    /**
     * desc：获取数据表字段名
     * author：wh
     * @param $tablename
     * @return array
     */
    function getTableFields($tablename){
        $dbname = config('database.database');
        $sql = "SELECT COLUMN_NAME column_name,COLUMN_COMMENT column_comment,DATA_TYPE data_type
FROM information_schema.columns WHERE TABLE_NAME='{$tablename}' AND table_schema='{$dbname}'";
        return array_column(Db::query($sql), 'column_name');
    }


}